package org.eclipse.epsilon.emc.rdbms.core.persistancemechanism;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.eclipse.epsilon.emc.rdbms.core.customexceptions.CustomException;
import org.eclipse.epsilon.emc.rdbms.core.imodel.DBReflexiveModelElement;


public class H2Database extends RelationalDatabase
{	
	/**
	* This instantiates the H2 database Object
	* @param URL Connection This is the database URL connection
	* @param serverName This is the name of the database server
	* @param databaseName This is the databaseName that will be used to connect to.
	*  
	*/			
	   public H2Database(String URLConnection, String serverName, String databaseName)
	   {
	  	 this.URLConnection=URLConnection;
	  	 this.serverName=serverName;
	  	 this.databaseName=databaseName;
	   }

	   
	   /**
		* This gets a connection to the database server based on the current H2Database Object
		* @return connection This returns a connection to the database.  
		*/				   
	public Connection getConnection()
	{
		try{
		     Class.forName("org.h2.Driver");
		       connection = java.sql.DriverManager.getConnection(URLConnection+serverName+"/"+databaseName+";");     	           
		}
		     catch(Exception e)
		   {
		      e.printStackTrace();
		      System.out.println("Error Trace in getConnection() : " + e.getMessage());
		    }   	 
		      return connection;
	}
	
	/**
	* This method returns the H2 string for starting a transaction
	* @return String to being a transaction.  
	*/				   
	@Override
	public String getBeginTransaction() 
	{
	
		return "BEGIN TRANSACTION";
	}

	/**
	* This method returns the H2 string for commit a transaction
	* @return String to commit a transaction.  
	*/				   
	@Override
	public String getCommitTransaction() 
	{
		return "COMMIT TRANSACTION";
	}

	/**
	* This method returns the H2 string for rollback a transaction
	* @return String to rollback a transaction.  
	*/				   
	@Override
	public String getRollbackTransaction() 
	{
		return "ROLLBACK TRANSACTION";
	}
	
	  public ResultSet getSQLTables(String tablename) 
	  {
		try
	       {
	    	connection= this.getConnection();  
			if(connection!=null)               
	            {  
				String[] type = new String[1];
            	type[0] = "TABLE";
	            	ResultSet rs = connection.getMetaData().getTables(databaseName.toUpperCase(), null, tablename.toUpperCase(), type); //Return a list of all the database tables.
	                return rs;    
	             } 
	            else throw new CustomException("Error: Database Connection set incorrectly"); 
	        }
	        catch(Exception e)
	        {
	             e.printStackTrace();
	        }
			return null;
	  } 
	  
	  
	  public ResultSet getPrimaryKey(String tablename)
		{
		       try
		       {
		            connection= this.getConnection();
		            if(connection!=null)
		            { 
		            	ResultSet rs = connection.getMetaData().getPrimaryKeys(null, null, tablename.toUpperCase());
		            	return rs;   
		            }
		            else throw new CustomException("Error: No Connection defined");
		        }
		        catch(Exception e)
		        {
		             e.printStackTrace();
		        }
		        return null;
		  }
	  
	  public ResultSet getSQLColumns(String tablename) 
	   {
	  	try
	  	{
	  		 connection= this.getConnection();
	           if(connection!=null)
	           {	      	        	   
	        	      DatabaseMetaData dbmd = connection.getMetaData();
	        	      dbmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
	        	      
	        	      ResultSet rs = dbmd.getColumns(databaseName.toUpperCase(), null,tablename.toUpperCase(),"%");
	        	return rs;      
	           } 
	           else throw new CustomException("WARNING: No Database Connection was set");
	      }
	  	catch(Exception e)
	      {
	           e.printStackTrace();
		 }
	      return null;
	   }

	  public ResultSet getExportedKeys(DBReflexiveModelElement foreignElement) throws SQLException
	  {
		  ResultSet primaryElementTableInfo = getSQLTables(foreignElement.getType());
		  String foreigndatabaseName=null;
		  String foreignschemaName=null;
		  String foreigntableName=null;
		  
          while(primaryElementTableInfo.next())
          {
        	  foreigndatabaseName = primaryElementTableInfo.getString("TABLE_CAT").toUpperCase();
        	  foreignschemaName = primaryElementTableInfo.getString("TABLE_SCHEM").toUpperCase();
        	  foreigntableName = primaryElementTableInfo.getString("TABLE_NAME").toUpperCase();
          }
        return   connection.getMetaData().getExportedKeys(foreigndatabaseName, foreignschemaName, foreigntableName);
	  }

	  
	  public ResultSet getImportedKeys(String element) throws SQLException
	  {
		  ResultSet primaryElementTableInfo = getSQLTables(element);
		  String primarydatabaseName=null;
		  String primaryschemaName=null;
		  String primarytableName=null;
		  
          while(primaryElementTableInfo.next())
          {
  			primarydatabaseName = primaryElementTableInfo.getString("TABLE_CAT").toUpperCase();
  			primaryschemaName = primaryElementTableInfo.getString("TABLE_SCHEM").toUpperCase();
  			primarytableName = primaryElementTableInfo.getString("TABLE_NAME").toUpperCase();
          }
        return   connection.getMetaData().getImportedKeys(primarydatabaseName, primaryschemaName, primarytableName);
	  }


	  
	  
	  
}
